package orest.common.dao;

import java.util.*;
import java.sql.*;
import javax.sql.*;
import orest.common.response.*;
import orest.common.valueobject.ExpertKjcgVal;
import orest.common.util.DataBase;
import orest.common.util.Tools;

/**
 *
 * @author admin
 */
public class ExpertJdcgDao {
    
    /**
     * Creates a new instance of ExpertKjcgVal
     */
    public ExpertJdcgDao() {
    }
    public boolean findJdcgByCondition(Response rp,String condition,int pageNo,int pageRecord,int total){
        boolean result = true;
        ArrayList al = new ArrayList();
        int iPrePageCount ;
        int tempRow = 0;
        Tools tool = new Tools();

        /* number of record in page */
        int startRow = (pageNo -1) * pageRecord;
        int endRow = pageNo * pageRecord;

        if(endRow > total)
            endRow = total;
        tempRow = 1;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        String rowcount = "select top  " + Integer.toString(endRow);
        String sql = "select ExpertID,KjcgID,Item_3,Item_191 from T_ExpertJdcg where " + condition;
       
        try {
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            rs = pstmt.executeQuery();
            while(rs.next ()){
                if(tempRow > startRow) {
                    ExpertKjcgVal kjcgVal = new ExpertKjcgVal();
                    kjcgVal.setExpertID(rs.getString("ExpertID"));
                    kjcgVal.setKjcgID(rs.getString("KjcgID"));
                    kjcgVal.setItem_3(rs.getString("Item_3"));
                    kjcgVal.setItem_186(rs.getString("Item_191"));
                    al.add (kjcgVal);
                }
                else 
                    tempRow++;
            }
                
            rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("ExpertJdcgDao have a exception!" + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getMyConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is ExpertJdcgDao and the " +
                                 "function is findJdcgByCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                 
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertJdcgDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertJdcgDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ExpertJdcgDao and" +
                            " the function is getJdcgByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }
   
    public boolean findTopJdcgByExpID(Response rp,String num,String expid) {

        boolean result = true;
        ArrayList al = new ArrayList();
        Tools tool = new Tools();

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top  " + num;
        String sql = "select KjcgID,Item_3,Item_191,Item_Year,AchieveType from T_ExpertJdcg where ExpertID='" +expid ;
        sql += "' order by KjcgID desc";


        /* sql string end */


        try{

            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                 ExpertKjcgVal expkjcgVal = new ExpertKjcgVal();
                 expkjcgVal.setKjcgID(rs.getString("KjcgID"));
                 expkjcgVal.setItem_3(rs.getString("Kjcg_Item_3"));
                 al.add (expkjcgVal);
            }


                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("ExpertKjcgDao have a exception!" + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is ExpertKjcgDao and the " +
                                 "function is findTopKjcgByExpID! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{

                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertJdcgDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertJdcgDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ExpertJdcgDao and" +
                            " the function is findTopJdcgByExpID, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

     
     public boolean findTopJdcgAllByExpID(Response rp,String num,String expid) {

        boolean result = true;
        ArrayList al = new ArrayList();
        Tools tool = new Tools();
        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top  " + num;
        String sql = "select * from T_ExpertJdcg where ExpertID='" + expid ;
        sql += "' order by KjcgID desc";


        /* sql string end */


        try{

            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                al.add (new ExpertKjcgVal(rs.getString ("KjcgID"),
                                     rs.getString ("Item_3"),
                                     rs.getString ("Item_191"),
                                     rs.getString ("Item_2"),
                                     rs.getString ("Item_126"),
                                     rs.getString ("Item_Year"),
                                     rs.getString ("Item_13_1s"),
                                     rs.getString ("item_9"),
                                     rs.getString ("Appraiser")));


            }
           rp.setValObjs (al);
           return true;
        }
        catch(SQLException e){
            System.out.println("ExpertJdcgDao have a exception!" + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is ExpertJdcgDao and the " +
                                 "function is findTopJdcgByExpID! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{

                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertJdcgDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertJdcgDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ExpertKjcgDao and" +
                            " the function is findTopKjcgByExpID, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }
}
